package Services.DBConn;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mysql.jdbc.Statement;

import Entity.Book;
import Entity.BookInvoice;
import Entity.Invoice;
import Entity.User;
import Entity.EntityMapper.EntityMapper;
import Services.WebService;

public class EntityManager {
	private DatabaseConnection dbConn;
	private EntityMapper entityMapper;

	public EntityManager() {
		this.dbConn = new DatabaseConnection();
		this.entityMapper = new EntityMapper();
	}

	public User newUser(User user) {
		try {
			if (this.checkConnection()) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("INSERT INTO User (username, password, salt, email, telephone, role, loyalty_point) VALUES (?, ?, ?, ?, ?, ?, ?)");
				this.dbConn.pstmt.setString(1, user.getUsername());
				this.dbConn.pstmt.setString(2, user.getPassword());
				this.dbConn.pstmt.setString(3, user.getSalt());
				this.dbConn.pstmt.setString(4, user.getEmail());
				this.dbConn.pstmt.setInt(5, user.getTelephone());
				this.dbConn.pstmt.setString(6, user.getRole());
				this.dbConn.pstmt.setInt(7, user.getLoyaltyPoint());

				this.dbConn.execute();
				
				return this.authentication(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public User authentication(User loginUser) {
		try {
			if (this.checkConnection()) {
				if (loginUser != null) {
					this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT * FROM User WHERE username = ? AND password = ?");
					this.dbConn.pstmt.setString(1, loginUser.getUsername());
					this.dbConn.pstmt.setString(2, loginUser.getPassword());

					if (this.dbConn.execute()) {
						ArrayList<User> userList = this.entityMapper.getUserResults(dbConn.getResults());
						if (userList != null && userList.size() > 0) {
							return userList.get(0);
						}
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public boolean checkUsernameIsUsed(String username) {
		try {
			if (this.checkConnection()) {
				if (username != null) {
					this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT * FROM User WHERE username = ?");
					this.dbConn.pstmt.setString(1, username);

					if (this.dbConn.execute()) {
						ArrayList<User> userList = this.entityMapper.getUserResults(dbConn.getResults());
						if (userList != null && userList.size()> 0) {
							return true;
						}
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean checkEmailIsUsed(String email) {
		try {
			if (this.checkConnection()) {
				if (email != null) {
					this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT * FROM User WHERE email = ?");
					this.dbConn.pstmt.setString(1, email);

					if (this.dbConn.execute()) {
						ArrayList<User> userList = this.entityMapper.getUserResults(dbConn.getResults());
						if (userList != null && userList.size()> 0) {
							return true;
						}
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public User getUser(int userID) {
		try {
			if (this.checkConnection()) {
				if (userID > 0) {
					this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT * FROM User WHERE id= ?");
					this.dbConn.pstmt.setInt(1, userID);
					if (this.dbConn.execute()) {
						ArrayList<User> userList = this.entityMapper.getUserResults(dbConn.getResults());
						if (userList != null && userList.size() > 0) {
							return userList.get(0);
						}
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public ArrayList<User> getUsers() {
		try {
			if (this.checkConnection()) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT * FROM User");
				if (this.dbConn.execute()) {
					ArrayList<User> userList = this.entityMapper.getUserResults(dbConn.getResults());
					if (userList != null && userList.size() > 0) {
						return userList;
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public void updateUser(User user) {
		try {
			if (this.checkConnection() && user.getId() > 0) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("UPDATE User SET username = ?, password = ?, salt = ?, email = ?, telephone = ?, role = ?, loyalty_point = ? WHERE id = ?");
				this.dbConn.pstmt.setString(1, user.getUsername());
				this.dbConn.pstmt.setString(2, user.getPassword());
				this.dbConn.pstmt.setString(3, user.getSalt());
				this.dbConn.pstmt.setString(4, user.getEmail());
				this.dbConn.pstmt.setInt(5, user.getTelephone());
				this.dbConn.pstmt.setString(6, user.getRole());
				this.dbConn.pstmt.setInt(7, user.getLoyaltyPoint());
				this.dbConn.pstmt.setInt(8, user.getId());

				this.dbConn.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void deleteUser(User user) {
		try {
			if (this.checkConnection() && user.getId() > 0) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("DELETE FROM User WHERE id= ?");
				this.dbConn.pstmt.setInt(1, user.getId());
				this.dbConn.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public Book newBook(Book book) {
		try {
			if (this.checkConnection()) {
				this.dbConn.pstmt = this.dbConn.conn
						.prepareStatement("INSERT INTO Book (title, description, price, quantity, publisher, language, no_of_viewed, no_of_purchased) VALUES (?, ?, ?, ?, ?, ?, 0, 0)");
				this.dbConn.pstmt.setString(1, book.getTitle());
				this.dbConn.pstmt.setString(2, book.getDescription());
				this.dbConn.pstmt.setFloat(3, book.getPrice());
				this.dbConn.pstmt.setInt(4, book.getQuantity());
				this.dbConn.pstmt.setString(5, book.getPublisher());
				this.dbConn.pstmt.setString(6, book.getLanguage());

				this.dbConn.execute();
				
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT last_insert_id() AS last_id FROM Book");
				this.dbConn.execute();
				
				this.dbConn.results.next();
				String temp = this.dbConn.results.getString("last_id");
				if(WebService.isInteger(temp))
					return this.getBook(Integer.parseInt(this.dbConn.results.getString("last_id")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Book getBook(int bookID) {
		try {
			if (this.checkConnection()) {
				if (bookID > 0) {
					this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT * FROM Book WHERE id= ?");
					this.dbConn.pstmt.setInt(1, bookID);
					if (this.dbConn.execute()) {
						ArrayList<Book> bookList = this.entityMapper.getBookResults(dbConn.getResults());
						if (bookList != null && bookList.size() > 0) {
							return bookList.get(0);
						}
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public Book getBookFromInvoice(int invoiceID) {
		try {
			if (this.checkConnection()) {
				if (invoiceID > 0) {
					this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT book_id FROM BookInvoice WHERE invoice_id= ?");
					this.dbConn.pstmt.setInt(1, invoiceID);
					this.dbConn.execute();
						
					this.dbConn.results.next();
					String temp = this.dbConn.results.getString("book_id");
					if(WebService.isInteger(temp))
						return this.getBook(Integer.parseInt(temp));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public ArrayList<Book> getBooks() {
		try {
			if (this.checkConnection()) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT * FROM Book");
				if (this.dbConn.execute()) {
					ArrayList<Book> bookList = this.entityMapper.getBookResults(dbConn.getResults());
					if (bookList != null && bookList.size() > 0) {
						return bookList;
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public ArrayList<Book> getMostViewedBooks() {
		try {
			if (this.checkConnection()) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT * FROM Book ORDER BY no_of_viewed DESC LIMIT 0, 10");
				if (this.dbConn.execute()) {
					ArrayList<Book> bookList = this.entityMapper.getBookResults(dbConn.getResults());
					if (bookList != null && bookList.size() > 0) {
						return bookList;
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public ArrayList<Book> getMostPurchasedBooks() {
		try {
			if (this.checkConnection()) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT * FROM Book ORDER BY no_of_purchased DESC LIMIT 0, 10");
				if (this.dbConn.execute()) {
					ArrayList<Book> bookList = this.entityMapper.getBookResults(dbConn.getResults());
					if (bookList != null && bookList.size() > 0) {
						return bookList;
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public ArrayList<Book> getMostRefundedBooks() {
		try {
			if (this.checkConnection()) {
				// need to add a field
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT * FROM Book ORDER BY no_of_refunded DESC LIMIT 0, 10");
				if (this.dbConn.execute()) {
					ArrayList<Book> bookList = this.entityMapper.getBookResults(dbConn.getResults());
					if (bookList != null && bookList.size() > 0) {
						return bookList;
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public void updateBook(Book book) {
		try {
			if (this.checkConnection() && book.getId() > 0) {
				this.dbConn.pstmt = this.dbConn.conn
						.prepareStatement("UPDATE Book SET title = ?, description = ?, price = ?, quantity = ?, publisher = ?, language = ?, no_of_viewed = ?, no_of_purchased = ? WHERE id = ?");
				this.dbConn.pstmt.setString(1, book.getTitle());
				this.dbConn.pstmt.setString(2, book.getDescription());
				this.dbConn.pstmt.setFloat(3, book.getPrice());
				this.dbConn.pstmt.setInt(4, book.getQuantity());
				this.dbConn.pstmt.setString(5, book.getPublisher());
				this.dbConn.pstmt.setString(6, book.getLanguage());
				this.dbConn.pstmt.setInt(7, book.getNoOfView());
				this.dbConn.pstmt.setInt(8, book.getNoOfPurchase());
				this.dbConn.pstmt.setInt(9, book.getId());

				this.dbConn.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void deleteBook(Book book) {
		try {
			if (this.checkConnection() && book.getId() > 0) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("DELETE FROM Book WHERE id= ?");
				this.dbConn.pstmt.setInt(1, book.getId());
				this.dbConn.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public Invoice newInvoice(Invoice invoice) {
		try {
			if (this.checkConnection()) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("INSERT INTO Invoice (user_id, timestamp, refund_request, is_refunded, remarks, attn, address, loyalty_point_used) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
				this.dbConn.pstmt.setInt(1, invoice.getUser().getId());
				this.dbConn.pstmt.setDate(2, (Date) invoice.getDate());
				this.dbConn.pstmt.setBoolean(3, invoice.isRefund());
				this.dbConn.pstmt.setBoolean(4, invoice.isIsRefunded());
				this.dbConn.pstmt.setString(5, invoice.getRemarks());
				this.dbConn.pstmt.setString(6, invoice.getAttn());
				this.dbConn.pstmt.setString(7, invoice.getAddress());
				this.dbConn.pstmt.setBoolean(8, invoice.isLoyaltyPointUsed());

				this.dbConn.execute();

				ResultSet rs = this.dbConn.pstmt.executeQuery("select last_insert_id() as last_id from Invoice");
				if(rs.first()){
					String temp = rs.getString("last_id");
					if(WebService.isInteger(temp))
						return this.getInvoice(Integer.parseInt(temp));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return invoice;
	}

	public Invoice getInvoice(int invoiceID) {
		try {
			if (this.checkConnection() && invoiceID > 0) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT * FROM Invoice WHERE id= ?");
				this.dbConn.pstmt.setInt(1, invoiceID);
				if (this.dbConn.execute()) {
					ArrayList<Invoice> invoiceList = this.entityMapper.getInvoiceResults(dbConn.getResults());
					if (invoiceList.size() > 0) {
						return invoiceList.get(0);
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public ArrayList<Invoice> getInvoices() {
		try {
			if (this.checkConnection()) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT * FROM Invoice");
				if (this.dbConn.execute()) {
					ArrayList<Invoice> invoiceList = this.entityMapper.getInvoiceResults(dbConn.getResults());
					if (invoiceList.size() > 0) {
						return invoiceList;
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public ArrayList<Invoice> getInvoices(int userID) {
		try {
			if (this.checkConnection()) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT * FROM Invoice WHERE user_id = ?");
				this.dbConn.pstmt.setInt(1, userID);
				if (this.dbConn.execute()) {
					ArrayList<Invoice> invoiceList = this.entityMapper.getInvoiceResults(dbConn.getResults());
					if (invoiceList.size() > 0) {
						return invoiceList;
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public ArrayList<Invoice> getRefundInvoices() {
		try {
			if (this.checkConnection()) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT * FROM Invoice WHERE refund_request = 1 AND is_refunded = 0");
				if (this.dbConn.execute()) {
					ArrayList<Invoice> invoiceList = this.entityMapper.getInvoiceResults(dbConn.getResults());
					if (invoiceList.size() > 0) {
						return invoiceList;
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public void updateInvoice(Invoice invoice) {
		try {
			if (this.checkConnection() && invoice.getId() > 0) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("UPDATE Invoice SET user_id = ?, timestamp = ?, refund_request = ?, is_refunded = ?, remarks = ?, attn = ?, address = ?, loyalty_point_used = ? WHERE id = ?");
				this.dbConn.pstmt.setInt(1, invoice.getUser().getId());
				this.dbConn.pstmt.setDate(2, (Date) invoice.getDate());
				this.dbConn.pstmt.setBoolean(3, invoice.isRefund());
				this.dbConn.pstmt.setBoolean(4, invoice.isIsRefunded());
				this.dbConn.pstmt.setString(5, invoice.getRemarks());
				this.dbConn.pstmt.setString(6, invoice.getAttn());
				this.dbConn.pstmt.setString(7, invoice.getAddress());
				this.dbConn.pstmt.setBoolean(8, invoice.isLoyaltyPointUsed());
				this.dbConn.pstmt.setInt(9, invoice.getId());
				
				this.dbConn.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void deleteInvoice(Invoice invoice) {
		try {
			if (this.checkConnection() && invoice.getId() > 0) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("DELETE FROM Invoice WHERE id= ?");
				this.dbConn.pstmt.setInt(1, invoice.getId());

				this.dbConn.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public BookInvoice newBookInvoice(BookInvoice bookInvoice) {
		try {
			if (this.checkConnection()) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("INSERT INTO BookInvoice (book_id, invoice_id, quantity) VALUES (?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
				this.dbConn.pstmt.setInt(1, bookInvoice.getBook().getId());
				this.dbConn.pstmt.setInt(2, bookInvoice.getInvoiceID());
				this.dbConn.pstmt.setInt(3, bookInvoice.getQuantity());

				this.dbConn.execute();

				ResultSet rs = this.dbConn.pstmt.executeQuery("select last_insert_id() as last_id from BookInvoice");
				
				if(rs.first()){
					String temp = rs.getString("last_id");
					if(WebService.isInteger(temp))
						return this.getBookInvoice(Integer.parseInt(temp));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public BookInvoice getBookInvoice(int invoiceID) {
		try {
			if (this.checkConnection()) {
				if (invoiceID > 0) {
					this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT * FROM BookInvoice WHERE invoice_id= ?");
					this.dbConn.pstmt.setInt(1, invoiceID);
					if (this.dbConn.execute()) {
						ArrayList<BookInvoice> bookInvoiceList = this.entityMapper.getBookInvoiceResults(dbConn.getResults());
						if (bookInvoiceList.size() > 0) {
							return bookInvoiceList.get(0);
						}
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public ArrayList<BookInvoice> getBookInvoices() {
		try {
			if (this.checkConnection()) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("SELECT * FROM BookInvoice");
				if (this.dbConn.execute()) {
					ArrayList<BookInvoice> bookInvoiceList = this.entityMapper.getBookInvoiceResults(dbConn.getResults());
					if (bookInvoiceList.size() > 0) {
						return bookInvoiceList;
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public void updateBookInvoice(BookInvoice bookInvoice) {
		try {
			if (this.checkConnection() && bookInvoice.getInvoiceID() > 0) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("UPDATE BookInvoice SET quantity = ? WHERE invoice_id = ? AND book_id = ?");
				this.dbConn.pstmt.setInt(1, bookInvoice.getQuantity());
				this.dbConn.pstmt.setInt(2, bookInvoice.getBook().getId());
				this.dbConn.pstmt.setInt(3, bookInvoice.getInvoiceID());

				this.dbConn.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void deleteBookInvoice(BookInvoice bookInvoice) {
		try {
			if (this.checkConnection() && bookInvoice.getInvoiceID() > 0) {
				this.dbConn.pstmt = this.dbConn.conn.prepareStatement("DELETE FROM BookInvoice WHERE invoice_id = ? AND book_id = ?");
				this.dbConn.pstmt.setInt(1, bookInvoice.getInvoiceID());
				this.dbConn.pstmt.setInt(2, bookInvoice.getBook().getId());

				this.dbConn.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private boolean checkConnection() {
		if(this.dbConn == null){
			System.out.println("DB Connection accidentally closed?");
			this.dbConn.setup();
		}
		
		return true;
	}

	public DatabaseConnection getDbConn() {
		return dbConn;
	}
}